/* to get the process_step */

ALTER FUNCTION FN_GET_PROCESS_STEP_ID( @PART_NUMBER VARCHAR(50), @PS_TYPE_NAME VARCHAR(50), @PS_TYPE VARCHAR(50) )
 
 RETURNS  @FN_RESULT TABLE( PROCESS_STEP_ID INT )

AS
BEGIN	
	DECLARE @TEMP_PROCESSSTEP_ID INT
	DECLARE @LOCATION_ID INT 

	--CHECK WEATHER PROCESSSTEP_ID IS EXIST IN PROCESS_STEP TABLE, IF NOT FOUND BRING IT FROM PRODUCT_MAP_TABLE TABLE
	SELECT @TEMP_PROCESSSTEP_ID = PS.PROCESSSTEP_ID FROM PRODUCT P, PROCESS_STEP PS WHERE  P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND P.PART_NUMBER = @PART_NUMBER AND PS_TYPE_NAME = @PS_TYPE_NAME AND PS_TYPE = @PS_TYPE
	IF @TEMP_PROCESSSTEP_ID IS NULL
	BEGIN
		
		SELECT @TEMP_PROCESSSTEP_ID = PS.PROCESSSTEP_ID FROM PRODUCT_MAP_TABLE PMT, PROCESS_STEP PS 
		WHERE PMT.PROCESSSTEP_ID=PS.PROCESSSTEP_ID AND PMT.PART_NUMBER = @PART_NUMBER AND PS_TYPE_NAME = @PS_TYPE_NAME AND PS_TYPE = @PS_TYPE
	END
	IF @TEMP_PROCESSSTEP_ID > 0
	BEGIN
		INSERT INTO @FN_RESULT VALUES (@TEMP_PROCESSSTEP_ID) 
	END
RETURN
END
